<?php
/**
 * report.php
 * @author ranuandra
 *
 * 12 Agu 2011 15:02:30
 *
 * 
 */

class Controller_Report extends Kohana_Polreg
{
	public $auth_required = array('login');
	public function action_index()
	{
		$datas = array();
		
		$post = array(
					'area_id' 	=> 0,
					'model_id'	=> array(),
					'type_id'	=> array(),
					'jumlah'	=> 0,
					'merk'		=> 0,
					'drthn'		=> '',
					'kethn'		=> ''
				);
		
		if(isset($_POST['area_id']) or isset($_POST['model_id']) or isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		
	    //print_r($post['model_id']);
	   // exit;
		if(isset($_POST['submit']))
		{
			$polreg = new Model_Report();
			//$datas = $polreg->data_by_type();
			$datas = $polreg->data_by_type($post);
		}

	    //print_r($datas);
	    //exit;
		
		$header = View::factory('report/header');
		$header->action = $this->action;
		$header->frmarea = true;
		$header->frmmodel = true;
		$header->frmjumlah = true;
		$header->frmmerk = true;
		$header->frmrange = true;
		$header->frmtype = true;
		$header->excel = true;
		$header->post = $post;
 		$container = View::factory('report/type');
 		$container->post = $post;
 		if(!$_POST)
		{
			$container->header = $header;
		}
		$container->datas = $datas;
		
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{	$container->ajax = 0;
			$menu = View::factory('module/menu');
			$this->template->menu = $menu;
			$this->template->container = $container;
			//$this->template->title = "Test";
		}		
		
	}
	
	public function action_test()
	{
/*		
		$test = new Model_Polreg();
		$coba = $test->cek_db();
		print_r($coba);*/
		$test = DB::select()->from('polregs')->execute();
		print_r($test);
		exit;
	}
	
	public function action_detail()
	{
		$datas = array();
		
		$post = array(
					'customer'  => 0,
					'area_id' 	=> 0,
					'model_id'	=> array(),
					'type_id'	=> array(),
					'jumlah'	=> 0,
					'merk'		=> 0,
					'drthn'		=> '',
					'kethn'		=> ''
				);
		
		if(isset($_POST))
		{
			$post = $_POST;
		}
		
	    //print_r($post['model_id']);
	   // exit;
	   $polreg = new Model_Report();

	    //print_r($datas);
	    //exit;
		
		$hidden = array('customer'=>$post['customer']);
		$customer = ORM::factory('customer')->where('id', '=', $post['customer'])->find();
		
		$tahun = $polreg->history_tahun($post['customer']);
		
		//print_r($tahun);
		
		//ORM::factory('polregs')->where('id', '=', $post['customer'])->group_by(DB::expr("YEAR('tgl_daftar')"))->find_all();
		
		$header = View::factory('report/header');
		
		$header->action = $this->action;
		$header->frmarea = true;
		$header->frmmodel = true;
		$header->frmjumlah = true;
		$header->frmmerk = true;
		$header->frmrange = true;
		$header->frmtype = true;
		$header->excel = true;
		$header->post = $post;
		$header->hidden = $hidden;
 		$container = View::factory('report/detail');
 		$container->header = $header;
 		$container->post = $post;
 		$container->customer = $customer;
 		$container->tahuns = $tahun;
		
 		
 		if(!$_POST)
		{
			$container->header = $header;
		}
		
		//$container->datas = $datas;
		//print_r($datas);
		
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{	$container->ajax = 0;
			$menu = View::factory('module/menu');
			$this->template->menu = $menu;
			$this->template->container = $container;
			//$this->template->title = "Test";
		}			
	}
	
	public function action_history()
	{
		if(isset($_POST['area_id']) or isset($_POST['model_id']) or isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		else
		{
			$post = array(
						'area_id' 	=> 0,
						'model_id'	=> 0,
						'jumlah'	=> 0,
						'merk'		=> 0,
						'drthn'		=> '',
						'kethn'		=> ''
					);
		}
		$action = $this->request->action();
		
		//$polreg = new Model_Report();
		//$datas = $polreg->data_by_type();
		//$datas = $polreg->data_by_customer($post);
		
		$header = View::factory('report/header');
		$header->action = $action;
		$header->frmmodel = true;
		$header->frmrange = true;
		$header->excel = true;
		$header->post = $post;
 		$container = View::factory('report/history');
 		$container->post = $post;
		$container->header = $header;
		//$container->datas = $datas;
		echo $container;
		exit;
		
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{	$container->ajax = 0;
			$menu = View::factory('module/menu');
			$this->template->menu = $menu;
			$this->template->container = $container;
		}		
		
	}
	
	public function action_search()
	{
		$datas = array();
		
		$post = array(
					'area_id' 	=> 0,
					'model_id'	=> array(),
					'type_id'	=> array(),
					'jumlah'	=> 0,
					'merk'		=> 0,
					'drthn'		=> '',
					'kethn'		=> ''
				);
		
		if(isset($_POST['area_id']) or isset($_POST['model_id']) or isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		
	    //print_r($post['model_id']);
	   // exit;
		if(isset($_POST['submit']))
		{
			$polreg = new Model_Report();
			//$datas = $polreg->data_by_type();
			$datas = $polreg->data_by_customer($post);
		}

	    //print_r($datas);
	    //exit;
		
		$header = View::factory('report/header');
		$header->action = $this->action;
		$header->frmarea = true;
		$header->frmmodel = true;
		$header->frmjumlah = true;
		$header->frmmerk = true;
		$header->frmrange = true;
		$header->frmtype = true;
		$header->excel = true;
		$header->post = $post;
 		$container = View::factory('report/customer');
 		$container->post = $post;
 		if(!$_POST)
		{
			$container->header = $header;
		}
		$container->datas = $datas;
		
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{	$container->ajax = 0;
			$menu = View::factory('module/menu');
			$this->template->menu = $menu;
			$this->template->container = $container;
			//$this->template->title = "Test";
		}		
	}
	
	public function action_customer()
	{
		$datas = array();
		
		$post = array(
					'area_id' 	=> 0,
					'model_id'	=> array(),
					'type_id'	=> array(),
					'jumlah'	=> 0,
					'merk'		=> 0,
					'drthn'		=> '',
					'kethn'		=> ''
				);
		
		if(isset($_POST['area_id']) or isset($_POST['model_id']) or isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		
	    //print_r($post['model_id']);
	   // exit;
	   	$report = array();
		if(isset($_POST['submit']))
		{
			$polreg = new Model_Report();
			//$datas = $polreg->data_by_type();
			$datas = $polreg->data_by_customer($post);
			
/*			if(count($datas) > 0)
			{
				foreach($datas as $data)
				{
					$details = $polreg->detail_customer($post, 0);
					if(count($details)>0)
					{
						foreach($details as $detail)
						{
							array_push($detail, $data);
							$report[] = $data;
						}
					}
				}
			}*/
		}

	    //print_r($datas);
	   //exit;
		
		$header = View::factory('report/header');
		$header->action = $this->action;
		$header->frmarea = true;
		$header->frmmodel = true;
		$header->frmjumlah = true;
		$header->frmmerk = true;
		$header->frmrange = true;
		$header->frmtype = true;
		$header->excel = true;
		$header->post = $post;
 		$container = View::factory('report/customer');
 		$container->post = $post;
 		if(!$_POST)
		{
			$container->header = $header;
		}
		$container->datas = $datas;
		
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{	$container->ajax = 0;
			$menu = View::factory('module/menu');
			$this->template->menu = $menu;
			$this->template->container = $container;
			//$this->template->title = "Test";
		}		
	}
	
	public function action_typechart()
	{
		//$post = $_POST;
		if(isset($_POST['area_id']) && isset($_POST['merk']) && isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		else
		{
			$post = array(
						'area_id' 	=> '',
						'merk'		=> '',
						'jumlah'	=> ''
					);
		}
		//$type = ORM::factory('type')->limit(10)->find_all();
		$polreg = DB::select( 'polregs.type_id', array('COUNT("polregs.type_id")', 'jumlah'))->from('polregs')
				->join('types', 'INNER')->on('types.id','=','polregs.type_id');
		if($post['area_id'])
		{
			$polreg = $polreg->where('polregs.area_id', '=', $post['area_id']);
		}
		if($post['merk']!="")
		{
			$polreg = $polreg->where('types.merk_id', '=', $post['merk']);
		}
            $polreg	= $polreg->group_by('polregs.type_id')
								->limit(10)
								->order_by('jumlah', 'DESC')
								->execute();
				
		//$url = URL::base().'report/typechart/'.$post['area_id'].'/'.$post['merk'].'/'.$post['jumlah'];
		//print_r($polreg);
		//$container->header = View::factory('report/header');
		//$container->datas = $type;
		$action = $this->request->action();
		$header = View::factory('report/header');
		$header->action = $action;
		$header->post = $post;
		$header->frmarea = true;
		$header->frmmerk = true;
		$header->frmjumlah = true;
		$header->excel = true;
		//$header->chart = $url;
		$container = View::factory('report/typechart');
		$container->datas = $polreg;
		$container->header = $header;
		$this->template->menu = View::factory('module/menu');
		$this->template->container = $container;			
	}
	
	public function action_typelist()
	{
		$this->auto_render = false;
		$merk = $_POST['merk'];
		$model = $_POST['model'];
		
		$arrmodels = array();
		$arrmerks = array();	
		if($model!='null')
		{
			$arrmodels = split(",", $model);
		}
		if($merk!='null')
		{
			$arrmerks = split(",", $merk);
		}
		
		//print_r($arrmodel);
		
		$type = ORM::factory('type');
		
		if (count($arrmodels)> 0)
		{
			$type = $type->where_open();
			foreach($arrmodels as $arrmodel)
			{
				$type = $type->or_where('model_id', '=', $arrmodel);
			}
			$type = $type->where_close();
		}

		
		
		if (count($arrmerks)> 0)
		{
			$type = $type->where_open();
			foreach($arrmerks as $arrmerk)
			{
				$type = $type->or_where('merk_id', '=', $arrmerk);
			}
			$type = $type->where_close();
		}
				
		
		//print_r($type);
		$type = $type->order_by('type')->find_all()->as_array('id', 'type');
		//$container = View::factory('report/typechart');
		//$container->header = View::factory('report/header');
		//$container->datas = $type;
		//$this->template->menu = View::factory('module/menu');
		//$this->template->container = $container;

		$container = View::factory('report/typelist');
		$container->typelist = $type;
		echo $container;
		exit;
			
	}
	
	public function action_typexml()
	{
		$this->auto_render = false;
		//$polreg = ORM::factory('polreg')->select('polregs.type_id', array('COUNT("polregs.type_id")', 'jumlah'))->limit(10)->order_by('jumlah', 'DESC')->find_all();

		$polreg = DB::select( 'polregs.type_id', array('COUNT("polregs.type_id")', 'jumlah'))->from('polregs')
								->group_by('polregs.type_id')
								->limit(10)
								->order_by('jumlah', 'DESC')
								->execute();
		$container = View::factory('report/xml/typexml');
		$container->datas = $polreg;
		echo $container;
		exit;
	}
	
	public function action_detail1($data = "")
	{
		//$hidden = array();
		if(isset($_POST['area_id']) or isset($_POST['model_id']) or isset($_POST['jumlah']))
		{
			$post = $_POST;
		}
		else
		{
			$post = array(
						'area_id' 	=> 0,
						'model_id'	=> 0,
						'jumlah'	=> 0,
						'merk'		=> 0,
						'drthn'		=> '',
						'kethn'		=> ''
					);
		}
		//dari link
		if($data != "")
		{
			list($id, $model) = split('/', $data);

			
		}
		//jika ada field hidden maka ambil parameter
		if(isset($_POST['model']) && isset($_POST['id']))
		{
			$id = $_POST['id'];
			$model = $_POST['model'];
		}

		$hidden = array('id' => $id,
							'model' => $model,
							);
		
		$customer = ORM::factory('customer')->where('id','=',$id)->find();
		
		$datas = DB::select( array(DB::expr('YEAR(polregs.tgl_daftar)'), 'tahun'))->from('polregs')
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->where('polregs.customer_id', '=', $id);
		if($model > 0)
		{
			$datas = $datas->where('types.model_id', '=', $model);
		}
		$datas = $datas->group_by(DB::expr('YEAR(polregs.tgl_daftar)'))
					   ->execute();
				   
	//print_r($datas);
		

/*		$dataarray = array();						
		$datamobil = array();
								
		foreach($datas as $data )
		{
			$typemobil = ORM::factory('type')->where('id', '=', $data['type_id'])->find();
			$jumlahmobil = DB::select(array('COUNT("polregs.type_id")', 'jumlah'))->from('polregs')
								->where('polregs.customer_id', '=', $id)
								->where('polregs.type_id', '=', $data['type_id'])
								->group_by('polregs.type_id')
								->execute();
			$datamobil['id'] = $typemobil->id;
			$datamobil['type'] = $typemobil->type;
			$datamobil['merk'] = $typemobil->merk->merk;
			$datamobil['model']	= $typemobil->model->model;
			$datamobil['jumlah'] = $jumlahmobil[0]['jumlah'];
			$datamobil['custid'] = $id;
			
			$dataarray[] = $datamobil;			
		}*/
		

		//$polreg = ORM::factory('polreg')->select('polregs.type_id')->where('polregs.customer_id', '=', $id)->group_by('polregs.type_id')->find_all();
		//$polreg = $customer->polreg->group_by('polregs.type_id')->find_all();
		//$polreg = ORM::factory('polreg')->where('customer_id', '=', $id)->group_by('polreg.type_id')->find_all();
		//print_r($polreg);
		//$polreg = ORM::factory('model')->where('customer_id', '=', $id)->find_all();
		//$polreg = new Model_Report();
		//$datas = $polreg->data_detail_customer($id);
/*		$polreg = DB::select( 'types.type','merks.merk', 'models.model', array('COUNT("type_id")','jumlah'))->from('polregs')
								->join('types', 'INNER')->on('types.id', '=', 'polregs.type_id')
								->join('merks', 'INNER')->on('merks.id', '=','types.merk_id')
								->join('models', 'INNER')->on('models.id', '=','types.model_id')
								->group_by('polregs.type_id')
								->order_by('jumlah', 'DESC')
								->execute();*/
		
		/*$polreg = DB::select( 'polregs.type_id')->from('polregs')
								->where('customer_id', '=', $id)
								->group_by('polregs.type_id')
								->execute();*/
								

		
		//print_r($datas);
		
		//$polreg = ORM::factory('customer')->where('id','=', $id)->find();
		
		
		$container = View::factory('report/detail');
		//$container->header = View::factory('report/header');
		
		$header = View::factory('report/header');
		$header->action = $this->action;
		if(isset($hidden))
		{
			$header->hidden = $hidden;
		}
		//$header->frmarea = true;
		$header->frmrange = true;
		$header->post = $post;
		
		$container->model = $model;
		$container->header = $header;
		$container->customer = $customer;
		$container->datas = $datas;
		if(Request::current()->is_ajax() == $this->request)
		{	
			$container->ajax = 1;
			echo $container;
			exit;
		}
		else 
		{
			$this->template->container = $container;
			$this->template->menu = View::factory('module/menu');
		}
		
	}
	
	public function action_customtype($id)
	{
		
		list($type, $customer) = explode('/', $id);
		
		$customer = ORM::factory('customer')->where('id','=',$customer)->find();
		$type = ORM::factory('type')->where('id', '=', $type)->find();
		
		$datas = ORM::factory('polreg')->where('customer_id', '=', $customer)->where('type_id', '=', $type)->order_by('polregs.tgl_daftar', 'ASC')->find_all();
		
		
		$container = View::factory('report/customtype');
		$container->customer = $customer;
		$container->datas = $datas;
		$container->type = $type;
		$this->template->container = $container;
		$this->template->menu = View::factory('module/menu');	
	}
	
	public function action_excel()
	{
		//require Kohana::find_file('classes', 'vendor/phpexcel/PHPExcel');
		
		$XLSX = new Spreadsheet();
		$data = array(
			'Users' => array(
				1 => array('ID', 'Name'),
				2 => array(1, 'Jane Doe'),
				3 => array(2, 'Fred Smith')
			),
		);
		$XLSX->set_data( $data, 1 );
		$file = $XLSX->save(array('name'=>'test'));
		
		$response = Response::factory();
		$response->send_file($file, true, 'coba.xlsx');
		//$XLSX->send(array('name'=>'test.xlxs'));	
	}
	
}